﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;

namespace ET.Monitor.DAL
{
    public class OtherDAL
    {
        string strSql = "", strErrMsg = "";
        object obj = null;
        DataTable dt = new DataTable();
        bool judge = false;
        DataSet ds = new DataSet();
        DataTable dtList = new DataTable();


        InvokeWebService iw = new InvokeWebService();
        string rtdbRelationAddress = "";
        string rtdbRealTimeAddress = "";
        string rtdbNameSpace = "";
        public OtherDAL()
        {
            rtdbRelationAddress = ConfigurationManager.ConnectionStrings["RelationURL"].ConnectionString;
            rtdbRealTimeAddress = ConfigurationManager.ConnectionStrings["RealTimeURL"].ConnectionString;
            rtdbNameSpace = ConfigurationManager.ConnectionStrings["NameSpace"].ConnectionString;
        }

        #region 上传文件
        public bool AddModel(string user, string name, string orgid, string type, byte[] connect)
        {
            int num = GetMax() + 1;

            dtList = new DataTable();
            dtList.Columns.Add("key");
            dtList.Columns.Add("value");

            DataRow dr = dtList.NewRow();

            dr = dt.NewRow();
            dr["key"] = "T_REPORTID";
            dr["value"] = num;
            dtList.Rows.Add(dr);

            dr = dt.NewRow();
            dr["key"] = "T_REPORTDESC";
            dr["value"] = name;
            dtList.Rows.Add(dr);

            dr = dt.NewRow();
            dr["key"] = "T_USERID";
            dr["value"] = user;
            dtList.Rows.Add(dr);

            dr = dt.NewRow();
            dr["key"] = "T_ORGID";
            dr["value"] = orgid;
            dtList.Rows.Add(dr);

            dr = dt.NewRow();
            dr["key"] = "T_TYPE";
            dr["value"] = type;
            dtList.Rows.Add(dr);

            dr = dt.NewRow();
            dr["key"] = "B_REPORTTEMPLATE";
            dr["value"] = connect;
            dtList.Rows.Add(dr);
            ds.Tables.Add(dtList);

            //List<Hashtable> list = new List<Hashtable>();
            //Hashtable ht = new Hashtable();
            //ht.Add("key", "T_REPORTID");
            //ht.Add("value", num);
            //list.Add(ht);

            //ht = new Hashtable();
            //ht.Add("key", "T_REPORTDESC");
            //ht.Add("value", name);
            //list.Add(ht);

            //ht = new Hashtable();
            //ht.Add("key", "T_USERID");
            //ht.Add("value", user);
            //list.Add(ht);

            //ht = new Hashtable();
            //ht.Add("key", "T_ORGID");
            //ht.Add("value", orgid);
            //list.Add(ht);

            //ht = new Hashtable();
            //ht.Add("key", "T_TYPE");
            //ht.Add("value", type);
            //list.Add(ht);
            judge = (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "UpDocument", new object[] { ds, "T_INFO_REPORT_USERTEMPLATE", "B_REPORTTEMPLATE", connect });
            //judge = db.UpDocument(list, "T_INFO_REPORT_USERTEMPLATE", "B_REPORTTEMPLATE", connect);

            return judge;
        }
        #endregion

        #region 获取最大编号
        public int GetMax()
        {
            strSql = "select isnull(Max(T_REPORTID),0) from T_INFO_REPORT_USERTEMPLATE";
            obj = iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg });
            return Convert.ToInt32(obj);
        }
        #endregion

        #region 修改模板
        public bool EditModel(string id, string user, string desc, string type, string orgid)
        {
            strSql = "update T_INFO_REPORT_USERTEMPLATE set T_ORGID='" + orgid + "',T_TYPE='" + type + "',T_REPORTDESC='" + desc + "' where T_REPORTID='" + id + "' and T_USERID='" + user + "'";

            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });

        }
        #endregion

        #region 获取上传模板
        public DataTable GetModel(int pageCount, int pageIndex, string user)
        {
            strSql = "";
            strSql += "select top " + pageCount + " row_number() over(order by ID_KEY asc)as ";
            strSql += "ID,ID_KEY,T_REPORTID,T_REPORTDESC,T_USERID,T_TYPE,T_ORGID from T_INFO_REPORT_USERTEMPLATE where T_USERID='" + user + "' and ID_KEY not in";
            strSql += "(select top " + (pageCount * (pageIndex - 1)) + " ID_KEY from T_INFO_REPORT_USERTEMPLATE order by ID_KEY desc) order by ID_KEY desc";

            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        public int GetModelPageCount(string user)
        {
            strSql = "select count(*) from T_INFO_REPORT_USERTEMPLATE where T_USERID='" + user + "'";
            obj = iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg });
            return Convert.ToInt32(obj);
        }
        #endregion

        #region 删除模板
        public bool DelModel(string user, string id)
        {
            strSql = "delete from T_INFO_REPORT_USERTEMPLATE where T_USERID='" + user + "' and T_REPORTID='" + id + "'";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion

        #region Excel 导入数据库
        public bool AddExcel(DataSet ds, string tName)
        {
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "AddTable", new object[] { ds, tName });
        }
        #endregion

        #region 获取测点信息

        public DataTable GetPoints()
        {
            strSql = "select T_PARAID,T_PARADESC_EN,T_PARADESC_CN,T_SQL,T_REALTIME,T_PARATYPE from T_BASE_REPORTPARAID";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        public DataTable GetPoints(string id)
        {
            strSql = "select T_PARAID,T_PARADESC_EN,T_PARADESC_CN,T_SQL,T_REALTIME from T_BASE_REPORTPARAID where T_ORGID='" + id + "'";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }

        public DataTable GetPoints(string id, int pageCount, int pageIndex)
        {
            strSql = @" select top " + pageCount + " ID_KEY,T_PARAID,T_PARADESC_EN,T_PARADESC_CN,T_SQL,T_REALTIME from T_BASE_REPORTPARAID where ID_KEY not in(";
            strSql += "select top " + (pageCount * (pageIndex - 1)) + " ID_KEY from  T_BASE_REPORTPARAID order by ID_KEY asc)  order by ID_KEY asc";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        public int GetPointsPageCount(string id)
        {
            if (id.IndexOf("'") > -1)
                strSql = "select count(*) from T_BASE_REPORTPARAID where T_ORGID in(" + id + ")";
            else
                strSql = "select count(*) from T_BASE_REPORTPARAID where T_ORGID='" + id + "'";
            obj = iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg });
            return Convert.ToInt32(obj);
        }

        #endregion

        #region chart Model Page
        public DataTable GetChartModel(string id, string user, int pageCount, int pageIndex)
        {
            if (id == "")
            {
                strSql = @"select top " + pageCount + " ID_KEY,T_CHARTID,T_CHARTDESC,T_CHARTTYPE,";
                strSql += "T_ORGID,T_MODEL,T_ATTRIBUTE,T_PARAID,T_IMG from T_BASE_REPORTCHARTID where T_USERID='" + user + "' and ID_KEY not in (";
                strSql += "select top " + (pageCount * (pageIndex - 1)) + " ID_KEY from T_BASE_REPORTCHARTID order by ID_KEY desc ) order by ID_KEY desc";
            }
            else
            {
                strSql = @"select top " + pageCount + " ID_KEY,T_CHARTID,T_CHARTDESC,T_CHARTTYPE,";
                strSql += "T_ORGID,T_MODEL,T_ATTRIBUTE,T_PARAID,T_IMG from T_BASE_REPORTCHARTID where T_USERID='" + user + "' and T_ORGID='" + id + "' and ID_KEY not in (";
                strSql += "select top " + (pageCount * (pageIndex - 1)) + " ID_KEY from T_BASE_REPORTCHARTID order by ID_KEY desc ) order by ID_KEY desc";
            }
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }

        public int GetChartModelPageCount(string id, string user)
        {
            strSql = "select count(*) from T_BASE_REPORTCHARTID where T_USERID='" + id + "'";
            obj = iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg });
            return Convert.ToInt32(obj);
        }
        #endregion

        #region 获取模板信息
        public DataTable GetModelInfo(string id, string user)
        {
            strSql = "select B_REPORTTEMPLATE From T_INFO_REPORT_USERTEMPLATE where T_USERID='" + user + "' and T_REPORTID='" + id + "'";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion

        #region 执行SQL语句
        public DataTable GetSqlVal(string sql)
        {
            return (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { sql, strErrMsg });
        }
        #endregion

        #region 获取已有 Chart 模板数量
        public int GetChartModelCount()
        {
            strSql = "select isnull(count(*),0) from T_BASE_REPORTCHARTID";
            obj = iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg });
            return Convert.ToInt32(obj);
        }
        #endregion

        #region 插入 Chart 模板
        public bool AddChartModel(string cId, string cType, string name, string modelName, string attribute, string paraId)
        {
            strSql = "insert into T_BASE_REPORTCHARTID(T_CHARTID,T_CHARTTYPE,T_CHARTDESC,T_MODEL,T_ATTRIBUTE,T_PARAID) values";
            strSql += "('" + cId + "','" + cType + "','" + name + "','" + modelName + "','" + attribute + "','" + paraId + "')";
            judge = (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
            return judge;
        }
        #endregion

        #region 获取所有 Chart Model
        public DataTable GetChartModel()
        {
            strSql = "select *from T_BASE_REPORTCHARTID";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion


        #region 报表

        #region 获取报表类型
        public object GetReportType(string id)
        {
            strSql = "select distinct T_REPORTTYPE from T_INFO_SHEETPARA where T_REPORTID='" + id + "'";
            obj = iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg });
            return obj;
        }
        #endregion

        #region 获取 Word 报表
        public DataTable GetWordReport(string reportId, string orgid, DateTime time)
        {
            if (orgid != "select T_ORGID,T_REPORTID,B_REPORT,T_TIME from  T_INFO_REPORTCACHE where T_REPORTID ='" + reportId + "' and T_TIME='" + time + "' and T_ORGID='" + orgid + "'")
                strSql = "";
            else
                strSql = "select T_ORGID,T_REPORTID,B_REPORT,T_TIME from  T_INFO_REPORTCACHE where T_REPORTID ='" + reportId + "' and T_TIME='" + time + "''";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion

        #region 获取表格报表数据
        public DataTable GetTableReport(string reportId, string orgid, DateTime time)
        {
            if (orgid != "select T_ORGID,T_REPORTID,D_VALUE,T_TIME,T_PARAID From T_INFO_SHEETCACHE where T_REPORTID ='" + reportId + "' and T_TIME='" + time + "' and T_ORGID='" + orgid + "'")
                strSql = "";
            else
                strSql = "select T_ORGID,T_REPORTID,D_VALUE,T_TIME,T_PARAID From T_INFO_SHEETCACHE where T_REPORTID ='" + reportId + "' and T_TIME='" + time + "''";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion

        #region 获取报表所有参数
        public DataTable GetReport(string reportId, string orgId)
        {
            strSql = @"select t.T_ORGID,t.T_PARAID,t.T_REPORTID,t.T_PARADESC_EN,t.T_PARADESC_CN,t.T_UNITID,t.I_DECIMALPOINT,t.I_ORDER,t.T_PARATYPE,t.T_REPORTTYPE,t.T_SQL,t.T_REALTIME,t.SQLTYPE,t.T_FORMULA,t.T_FORMULAPARA,c.T_CHARTTYPE,c.T_MODEL,c.T_ATTRIBUTE From (
select pa.T_ORGID,pa.T_PARAID,pa.T_REPORTID,pa.T_PARADESC_EN,pa.T_PARADESC_CN,pa.T_UNITID,pa.I_DECIMALPOINT,pa.I_ORDER,pa.T_PARATYPE,pa.T_REPORTTYPE,r.T_SQL,r.T_REALTIME,r.T_PARATYPE SQLTYPE,r.T_FORMULA,r.T_FORMULAPARA
 From T_INFO_SHEETPARA pa left join T_BASE_REPORTPARAID r on pa.T_PARAID=r.T_PARAID) t left join T_BASE_REPORTCHARTID c
 on t.T_PARAID=c.T_CHARTID where t.T_REPORTID='" + reportId + "' and t.T_ORGID='" + orgId + "'";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }

        #endregion

        #region 获取公共池所有参数
        public DataTable GetReportPara()
        {
            strSql = "select *from T_BASE_REPORTPARAID";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        public DataTable GetRepartChart()
        {
            strSql = "select *from T_BASE_REPORTCHARTID";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion
        //
        //
        #endregion

        #region 参数预警值
        public object GetWarmingPara()
        {
            strSql = "select D_PARAWARINGLEVEL1 from T_BASE_SYSPARA";
            obj = iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg });
            return obj;
        }
        #endregion

        #region 参数故障值
        public object GetFaultPara()
        {
            strSql = "select D_PARAWARINGLEVEL2 from T_BASE_SYSPARA";
            obj = iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg });
            return obj;
        }
        #endregion

        #region 电站停机值
        public object GetStationHalt()
        {
            strSql = "select D_STATIONWARINGLEVEL1 from T_BASE_SYSPARA";
            obj = iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg });
            return obj;
        }
        #endregion

        #region 电站预警值
        public object GetStationWaringPara()
        {
            strSql = "select D_STATIONWARINGLEVEL2 from T_BASE_SYSPARA";
            obj = iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg });
            return obj;
        }
        #endregion


        #region 录入版本信息
        public bool AddVersion(int code, string versionCode, string desc, string type, string name)
        {
            strSql = "insert into T_INFO_VERSION(T_CODE,T_VERSIONCODE,T_DESC,T_TYPE,T_NAME) values";
            strSql += "('" + code + "','" + versionCode + "','" + desc + "','" + type + "','" + name + "')";
            judge = (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
            return judge;
        }
        #endregion


        #region 查询最新版本信息
        public DataTable GetVersion(string type)
        {
            strSql = "select TOP 1 T_CODE,T_VERSIONCODE,T_DESC,T_TYPE,T_NAME from T_INFO_VERSION where T_TYPE ='" + type + "' order by ID_KEY DESC";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion

        public bool runNonQuery(string sql)
        {
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { sql, strErrMsg });

        }
    }
}
